﻿set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 18, 2011
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InactivateLogoInformation]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Update LogoInformation SET IsActive = 0 WHERE Id = @Id;

	Return 1;

END




GO




ALTER TABLE commercial
ADD [Name] Varchar(250) NULL



GO


ALTER TABLE commercial
ADD Cost INT DEFAULT 0 NOT NULL

GO

ALTER TABLE commercial
ADD IsActive BIT DEFAULT 0 NOT NULL



GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 12, 2011
-- Description:	<Description,,>
-- exec [SearchCommercials] null,0
-- =============================================
ALTER PROCEDURE [dbo].[SearchCommercials]
	-- Add the parameters for the stored procedure here
	@Name varchar(250) = null,
	@TypeId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- select records
	SELECT CommercialId,[Name],TypeId,Cost,IsActive,CreatedDate as [Date]
	FROM Commercial
	WHERE ([Name] LIKE '%'+ @Name + '%'  or @Name is null)
		AND (TypeId = CASE WHEN @TypeId = 0 THEN TypeId ELSE @TypeId END)
	ORDER BY IsActive DESC,CreatedDate DESC


END




GO





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 11, 2011
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetCommercialById]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM Commercial c
	WHERE c.CommercialId = @Id
END



GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 13, 2011
-- Description:	<Description,,>
-- exec [DeleteCommercial] 101
-- =============================================
ALTER PROCEDURE [dbo].[DeleteCommercial]
	@TypeId int
AS
BEGIN
	
	UPDATE [dbo].[Commercial]
	SET IsActive = 0
	WHERE IsActive = 1
	AND TypeId = @TypeId
	--DELETE FROM [dbo].[Commercial]	WHERE TypeId = @TypeId
	RETURN @@RowCount;
END





GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 13, 2011
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SaveCommercial]
	@Id int
	,@Name varchar(250)
	,@Cost int
	,@TypeId int
	,@Script varchar(max)
	,@CreatedBy int
	,@CreatedDate datetime
AS
BEGIN

	IF (@Id > 0)
	BEGIN
		UPDATE [dbo].[Commercial]	
		SET 
			[TypeId] = @TypeId
			,[Script] = @Script
			,[CreatedBy] = @CreatedBy
			,[CreatedDate] = @CreatedDate
			,[Name] = @Name
			,[Cost] = @Cost
		WHERE CommercialId = @Id
	END
	ELSE
	BEGIN
		INSERT INTO [dbo].[Commercial]	
			([TypeId]
			,[Script]
			,[CreatedBy]
			,[CreatedDate]
			,[Name]
			,[Cost])
		 VALUES
			(@TypeId
			,@Script
			,@CreatedBy
			,@CreatedDate
			,@Name
			,@Cost)
	END

	RETURN @@RowCount;
END


GO





USE [dailyaza_db]
GO
/****** Object:  StoredProcedure [dbo].[SearchCommercials]    Script Date: 12/19/2011 11:14:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 12, 2011
-- Description:	<Description,,>
-- exec [SearchCommercials] null,0
-- =============================================
ALTER PROCEDURE [dbo].[SearchCommercials]
	-- Add the parameters for the stored procedure here
	@Name varchar(250) = null,
	@TypeId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- select records
	SELECT c.CommercialId,c.[Name],c.TypeId,l.[Description] as TypeName,c.Cost,c.IsActive,c.CreatedDate as [Date]
	FROM Commercial c
	INNER JOIN tblLookup l on l.LookUpID = c.TypeId
	WHERE ([Name] LIKE '%'+ @Name + '%'  or @Name is null)
		AND (TypeId = CASE WHEN @TypeId = 0 THEN TypeId ELSE @TypeId END)
	ORDER BY IsActive DESC,CreatedDate DESC


END



GO


USE [dailyaza_db]
GO
/****** Object:  StoredProcedure [dbo].[ActivateLogoInformation]    Script Date: 12/19/2011 11:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 16, 2011
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ActivateCommercial]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Update Commercial SET IsActive = 1 WHERE CommercialId = @Id;

	Return 1;

END



GO



USE [dailyaza_db]
GO
/****** Object:  StoredProcedure [dbo].[ActivateLogoInformation]    Script Date: 12/19/2011 11:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zain Shaikh
-- Create date: Dec 16, 2011
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InactivateCommercial]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Update Commercial SET IsActive = 0 WHERE CommercialId = @Id;

	Return 1;

END